'''
Created on Feb 2, 2013

@author: loic
'''

import sqlite3, os
from subprocess import Popen,PIPE

class mp3db(object):
    '''
    classdocs
    '''
    flags = set(['new', 'new_old', 'old', 'split', 'not_orig', 'best', 'enfants', 'flo'])
    def __init__(self):
        '''
        Constructor
        '''
        pass
    
    def add(self, path, artist, album, size, flags):
        pass
    
    def update(self, artist, album, path = None, size = None, flags = None, date = None):
        pass

    def init_index(self):
        pass
    
    def touch(self, artist, album):
        concat = u'%s_%s' % (artist, album)
        self.index[concat] = True
        
    def operate_on_dir(self, action, directory, specs=None):
        if directory == '.': directory = os.getcwd()
        one = os.listdir(directory)
        for artist in one:
            dir_artist = directory+'/'+artist
            if not os.path.isdir(dir_artist):
                continue
            two = os.listdir(dir_artist)
            for album in two:
                dir_album = dir_artist+'/'+album
                if not os.path.isdir(dir_album):
                    continue
                s1 = Popen(['du', '-s', dir_album], stdout=PIPE).stdout.read()
                s2 = s1.split()
                size = int(s2[0])
                if action == 'add':
                    self.add(dir_album, artist, album, size, specs)
                    self.touch(artist, album)
                elif action == 'update':
                    current = self.read(artist, album)
                    if current['size'] != size:
                        s = size
                    else:
                        s = None
                    if current['path'] != dir_album:
                        p = dir_album
                    self.update(artist, album, p, s, None, None)
                    self.touch(artist, album)
        
class mp3sqldb(mp3db):
    '''
    '''
    def __init__(self, filename):
        self.connection = sqlite3.connect(filename)
        self.connection.row_factory = sqlite3.Row

        self.cursor = self.connection.cursor()
    
    def close(self):
        self.connection.close()

    def create_db(self):
        
        create = '''CREATE TABLE folders (
         id INT NOT NULL PRIMARY KEY,
         concat VARCHAR(100),
         path TEXT,
         size INT UNSIGNED,
         date DATE''';
        for f in self.flags:
            create += ',\n{0} BOOLEAN'.format(f)
        create += ');'
        self.cursor.execute(create)
        self.connection.commit()
        
    def read(self, artist, album):
        concat = u'%s_%s' % (artist, album)
        self.cursor.execute('select concat from folders where concat=?', (concat,))
        result = self.cursor.fetchall()
        nb_result = len(result)
        if nb_result == 0:
            return None
        else:
            return result[0]
        
    def search(self, sql='select * from folders', params=()):
        self.cursor.execute(sql, params)
        return self.cursor.fetchall()
        
    def init_index(self):
        self.cursor.execute('select concat from folders')
        result = self.cursor.fetchall()
        self.index = {}
        for r in result:
            self.index[r['concat']] = False
        
    def add(self, path, artist, album, size, flags, date):
        concat = u'%s_%s' % (artist, album)
        self.cursor.execute('select concat from folders where concat=?', (concat,))
        if len(self.cursor.fetchall()) == 0:
            col = {}
            for spec in self.flags:
                if spec in flags:
                    col[spec] = True
                else:
                    col[spec] = False
#                        c.execute('insert into ? values (?)', (spec, concat))
            self.cursor.execute('insert into folders values (?, ?, ?, ?, ?, ?, ?, ?, ?)', (concat, path, size, col['new'], col['new_old'], col['not_orig'], col['old'], col['split'], col['enfants'], col['best'], col['flo']))
            self.connection.commit()
            print '%s\t%s_%s added' % (path, artist, album)
        else:
            print '%s\t%s_%s already exists' % (path, artist, album)
            
    def update(self, artist, album, path = None, size = None, flags = None, date = None):
        concat = u'%s_%s' % (artist, album)
        sql = 'update folders set '
        sqlvars = ()
        if (path != None):
            sql += 'path = ?'
            sqlvars = sqlvars + (path,)
        if (size != None):
            if len(sqlvars) > 0:
                sql += ', '
            sql += 'size = ?'
            sqlvars = sqlvars + (size,)
        if (date != None):
            if len(sqlvars) > 0:
                sql += ', '
            sql += 'date = ?'
            sqlvars = sqlvars + (date,)
        if (flags != None):
            for spec in self.flags:
                if len(sqlvars) > 0:
                    sql += ', '
                if spec in flags:
                    sql += '? = True'
                else:
                    sql += '? = False'
                sqlvars = sqlvars + (spec,)
        self.cursor.execute(sql, sqlvars)
        self.connection.commit()
        
            